Introduction to Structured Query Language (SQL)

This is my learning note of coursera course: [Introduction to Structured Query Language (SQL)][https://www.coursera.org/learn/intro-sql].

Overview

The difference between [sequential access][https://en.wikipedia.org/wiki/Sequential_access] and [random access][https://en.wikipedia.org/wiki/Random_access]: like a tape and a CD, or a scroll and a book.
SQL is the language we use to issue commands to the database: create/insert; read/select; update; delete data.

Basic SQL Operations

CREATE; USE; DESCRIBE; INSERT; DELETE; UPDATE; SELECT;
CREATE TABLE table (attribute type, ...);
INSERT INTO table (attribute) VALUES (value);
DELETE FROM table WHERE condition;
UPDATE table SET attribute=value WHERE condition;
SELECT columns FROM table WHERE condition LIKE condition ORDER BY attribute LIMIT rownum;

Data Types

Text: CHAR or VARCHAR, TINYTEXT (up to 255 chars), TEXT (65k), MEDIUMTEXT (16M), LONGTEXT (4G)
Binary (rarely used): BYTE (255 bytes), VARBINARY (65K), BLOB (also tiny, medium and long, same as text)
Integer: TINYINT (-128,128), SMALLINT (-32768,32768), INT or INTEGER, BIGINT
Floating: FLOAT (32-bit), DOUBLE (64-bit)
Dates: TIMESTAMP, DATETIME, DATE, TIME, NOW()

Keys and Indexes

user_id INT UNSIGNED NOT NULL AUTO_INCREMENT
set primary key and index by PRIMARY KEY() and INDEX(), usually using Hash or B-Trees for index

Normalization and Foreign Keys

(3NF) Do not replicate data. Instead, reference data. Use integers for keys and for references. Add a special ‘key’ columd to each table.
logical key: what the outside world uses for lookup (something we want to find and enter in the search box) (it can be changed)
Never use logical key as primary key!
CONSTRAINT FOREIGN KEY (xx_id) REFERENCES table (xx_id) ON SELETE CASCADE ON UPDATE CASCADE
FROM table1 JOIN table2 ON table1.xx_id = table2.xx_id

Many-to-Many Relationships